__all__ = ['Systems']
from ..storage import *
class Systems(Storage):

    SQL_DROP = ["""
        DROP TABLE IF EXISTS "systems"
    """]

    SQL_CREATE = ["""
        CREATE TABLE "systems" (
            "system_id"             integer         not null,
            "region_id"             integer         not null REFERENCES "regions" MATCH FULL ON DELETE RESTRICT ON UPDATE CASCADE,
            "security"              smallint        not null,
            "name"                  text            not null,
            PRIMARY KEY ("system_id")
        ) WITHOUT OIDS """,
        """ CREATE INDEX "systems_region_id" ON "systems" ("region_id") """,
        """ CREATE INDEX "systems_security"  ON "systems" ("security" ) """,
    ]

    SQL_INSERT = ["""
        INSERT INTO "systems" (
            "system_id",
            "region_id",
            "security",
            "name"
        ) VALUES (
            %(system_id)s,
            %(region_id)s,
            %(security)s,
            %(name)s
        )
    """]

    SQL_ANALYZE = ["""
        ANALYZE "systems"
    """]

    def drop(self):
        self.execute(self.SQL_DROP)

    def create(self):
        self.execute(self.SQL_CREATE)

    def insert(self, rows):
        self.execute(self.SQL_INSERT, rows)

    def analyze(self):
        self.execute(self.SQL_ANALYZE)

    def adapt(self, fetcher):
        return\
            map(lambda row: {
                'system_id'         : row['solarSystemID'   ],
                'region_id'         : row['regionID'        ],
                'security'          : round(row['security'        ] * 10) * 10,
                'name'              : row['solarSystemName' ],
            },
            filter(lambda row: row['solarSystemName'] != '',
            fetcher({
                'solarSystemID'     : int,
                'regionID'          : int,
                'solarSystemName'   : str,
                'security'          : float,
            }, 'mapSolarSystems')))
